﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

public partial class mb_activation : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Request.QueryString["usr"] == null || Request.QueryString["key"] == null)
                lbl_message.Text = wrapItAsMessage("&nbsp;<img src='Images/error.gif' border='0'>&nbsp;Invalid request.", true);
            else if (Request.QueryString["usr"].ToString() == "")
                lbl_message.Text = wrapItAsMessage("&nbsp;<img src='Images/error.gif' border='0'>&nbsp;Invalid User id.", true);
            else if (!new CommonFunctions().ValidateEmailID(Request.QueryString["usr"].ToString()))
                lbl_message.Text = wrapItAsMessage("&nbsp;<img src='Images/error.gif' border='0'>&nbsp;Invalid User id.", true);
            else if (Request.QueryString["key"].ToString() == "")
                lbl_message.Text = wrapItAsMessage("&nbsp;<img src='Images/error.gif' border='0'>&nbsp;Invalid activation key.", true);
            else
            {
                lbl_message.Text = "";
                lbl_result.Text = "&nbsp;<img src='Images/load.gif' border='0'>&nbsp;Please wait. Activation in process.";

                string sMsg = string.Empty;
                if (activateUserAccount(Request.QueryString["usr"].ToString(), Request.QueryString["key"].ToString(), ref sMsg))
                    lbl_result.Text = wrapItAsMessage("&nbsp;<img src='Images/tick4.gif' border='0'>&nbsp; Your account has been activated successfully. <br><br>Please click <a style=\"font-size: 8pt; font-family: Arial;\" href=\"default.aspx\" >here</a> to login.", false);
                else
                    lbl_result.Text = wrapItAsMessage(sMsg, true);
            }
        }
    }
    private string wrapItAsMessage(string msg, bool isError)
    {
        return "<div class=\"" + (isError == true ? "mbErrorMessage" : "mbMessage") + "\">" + msg + "</div>";
    }

    private bool activateUserAccount(string userid, string activekey, ref string sMsg)
    {
        Session.Remove("dbcon");
        bool retrn = false;
        HDDatabase HDD = new HDDatabase();
        PageFunctionsCustom PFC = new PageFunctionsCustom();
        DataTable dtDbServer = new DataTable();
        try
        {
            DataRow dr_company = HDD.GetTableRow("select a.* from ngcompany a, ngcompanyuser b where a.activation_key = '" + activekey + "' and b.user_id = '" + userid + "';");
            if (dr_company == null)
            {
                string sMaxBloxSupportEmail = string.Empty;
                if (ConfigurationManager.AppSettings["registration_notification_email"] != null && ConfigurationManager.AppSettings["registration_notification_email"].Trim() != "")
                    sMaxBloxSupportEmail = ConfigurationManager.AppSettings["registration_notification_email"];
                else
                    sMaxBloxSupportEmail = "maxblox_support@cellarstone.com";
                sMsg = "&nbsp;<img src='Images/error.gif' border='0'>&nbsp;Unable to process activation request. Please contact " + sMaxBloxSupportEmail;
                return retrn;
            }
            else if (Convert.ToBoolean(dr_company["isactive"].ToString()))
            {
                sMsg = "&nbsp;<img src='Images/info.gif' border='0'>&nbsp;Account already activated. <br><br>Please click <a style=\"font-size: 8pt; font-family: Arial;\" href=\"default.aspx\" >here</a> to login.";
                return retrn;
            }
            else
            {
                dynamic_DB_Allocate(userid, ref HDD);
                dtDbServer = HDD.GetDataTable("select * from ngcompanyserver where company_id = '" + dr_company["af_row_id"].ToString() + "';");
                dtDbServer.Rows[0]["dbpassword"] = security.encrypt(dtDbServer.Rows[0]["dbpassword"].ToString());
                dtDbServer.Rows[0]["company_id"] = dr_company["company_id"].ToString();
                if(PFC.processNewCompany(dtDbServer))
                {
                    string sUpdateModelDB = "update ngcompany set isactive = 1, activated_at = '" + DateTime.Now.ToUniversalTime().ToString() + "' where company_id = '" + dr_company["company_id"].ToString().Replace("'", "''") + "' and af_row_id = '" + dr_company["af_row_id"].ToString() + "';";
                    sUpdateModelDB += "UPDATE [ngcompanyuser] SET [user_id] = 'afadmin_" + dr_company["company_id"].ToString().Replace("'", "''") + "@cellarstone.com' WHERE [company_id] = '" + dr_company["company_id"].ToString().Replace("'", "''") + "' and af_row_id = 'e51e0327224a2eb9';";
                    sUpdateModelDB += "UPDATE [ngcompanyuser] SET [user_id] = 'update_" + userid.Replace("'", "''") + "' WHERE [company_id] = '" + dr_company["company_id"].ToString().Replace("'", "''") + "' and af_row_id = 'ea5ab41af276215a';";
                    sUpdateModelDB += "UPDATE [ngcompanyuser] SET [user_id] = 'view_" + userid.Replace("'", "''") + "' WHERE [company_id] = '" + dr_company["company_id"].ToString().Replace("'", "''") + "' and af_row_id = '322d53926d947f73';";
                    sUpdateModelDB += "DELETE FROM [ngcompanyuser] WHERE [company_id] = '" + dr_company["company_id"].ToString().Replace("'", "''") + "' and [af_row_id] = '650a333596bbf348' and [user_id] = 'admin';";
                    HDD.ExecuteCmdObj(new SqlCommand(sUpdateModelDB));

                    DataRow dr_user = HDD.GetTableRow("select * from ngcompanyuser where user_id = '" + userid + "' and company_id = '" + dr_company["company_id"].ToString() + "';");
                    
                    string strConn = @"Data Source='" + dtDbServer.Rows[0]["server"] + "';User Id='" + dtDbServer.Rows[0]["company_id"].ToString().Replace("'", "''") + "';Password='" + dtDbServer.Rows[0]["company_id"].ToString().Replace("'", "''") + "_123';Initial Catalog='"
                        + dtDbServer.Rows[0]["dbname"].ToString().Replace("'", "''") + "'";
                    HDDatabase hdCompDB = new HDDatabase(strConn);

                    string sUpdateCompanylDB = "UPDATE [sy_usr] SET [first_name] = '" + dr_user["first_name"].ToString().Replace("'", "''") + "', [last_name] = '" + dr_user["last_name"].ToString().Replace("'", "''") + "', [user_id] = '" + userid + "', [password] = '" + dr_user["password"].ToString().Replace("'", "''") + "', [hint_question] = '" + dr_user["hint_question"].ToString().Replace("'", "''") + "', [hint_answer] = '" + dr_user["hint_answer"].ToString().Replace("'", "''") + "', [custodian_filter] = 'mr' WHERE [af_row_id] = '650a333596bbf348';";
                    sUpdateCompanylDB += "UPDATE [sy_usr] SET [user_id] = 'afadmin_" + dr_company["company_id"].ToString().Replace("'", "''") + "@cellarstone.com', [last_name] = '" + dr_company["company_name"].ToString().Replace("'", "''") + "' WHERE [af_row_id] = 'e51e0327224a2eb9';";
                    sUpdateCompanylDB += "UPDATE [sy_usr] SET [user_id] = 'update_" + userid.Replace("'", "''") + "', [last_name] = '" + dr_company["company_name"].ToString().Replace("'", "''") + "' WHERE [af_row_id] = 'ea5ab41af276215a';";
                    sUpdateCompanylDB += "UPDATE [sy_usr] SET [user_id] = 'view_" + userid.Replace("'", "''") + "', [last_name] = '" + dr_company["company_name"].ToString().Replace("'", "''") + "' WHERE [af_row_id] = '322d53926d947f73';";
                    hdCompDB.ExecuteCmdObj(new SqlCommand(sUpdateCompanylDB));

                    sMsg = "";
                    retrn = true;
                }
                else
                {
                    sMsg = "";
                    retrn = false;
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("mb_activation: activateUserAccount: Error: " + ex.Message);
        }
        return retrn;
    }

    #region DB Allocation
    private void dynamic_DB_Allocate(string userId, ref HDDatabase hdd)
    {
        try
        {
            string company_af_row_ID = "";
            string server = ""; string db = ""; string username = ""; string pwd = "";
            company_af_row_ID = hdd.GetColumnValue("Select A.af_row_id from ngcompany A inner join ngcompanyuser B on A.company_id=B.company_id where B.user_id='" + userId + "'");
            getNewDBConnectionString(ref hdd, ref server, ref db, ref username, ref pwd);
            if (company_af_row_ID.Length > 0 && server.Length > 0)
            {
                hdd.ExecuteCmdObj(new SqlCommand("update ngcompanyserver set server='" + server + "', dbname='" + db + "', dbuser='" + username + "', dbpassword='" + pwd + "' where company_id='" + company_af_row_ID + "'"));
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in dynamic_DB_Allocate function. " + e.Message);
        }
    }
    private void getNewDBConnectionString(ref HDDatabase hdd, ref string serverName, ref string dbName, ref string userName, ref string password)
    {
        try
        {
            DataSet ds = new DataSet();
            ds = hdd.GetDataset("select * from sy_db_allocation where is_reserve_server=0 order by sequence asc");
            string tempConString = "";
            int allowed_db_count = 0; int allowed_schema_count = 0; string DB_Name = "";
            int existing_db_count = 0; int existing_schema_count = 0; bool found = false;
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    DataTable dtConfig = ds.Tables[0];
                    foreach (DataRow dr in dtConfig.Rows)
                    {
                        //tempConString = getConString(dr);
                        allowed_db_count = Convert.ToInt32(dr["db_count"].ToString());
                        allowed_schema_count = Convert.ToInt32(dr["schema_count_per_db"].ToString());
                        //existing_db_count = getDBCount(tempConString);
                        found = checkServerAvailability(dr, allowed_db_count, allowed_schema_count, ref DB_Name);
                        if (found)
                        {
                            serverName = dr["server"].ToString();
                            dbName = DB_Name;
                            userName = dr["db_username"].ToString();
                            password = dr["db_password"].ToString();
                            break;
                        }
                    }
                }
            }
            if (!found)//if not found set to reserved DB
            {
                ds = hdd.GetDataset("select * from sy_db_allocation where is_reserve_server=1");
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        serverName = ds.Tables[0].Rows[0]["server"].ToString();
                        dbName = ds.Tables[0].Rows[0]["db_key_string"].ToString();
                        userName = ds.Tables[0].Rows[0]["db_username"].ToString();
                        password = ds.Tables[0].Rows[0]["db_password"].ToString();
                    }
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in getNewDBConnectionString function. " + e.Message);
        }
    }
    private bool checkServerAvailability(DataRow drConfig, int allowedDBCount, int allowedSchemaCount, ref string DB_Name)
    {
        bool found = false;
        try
        {
            DataTable tblDatabases = getDatabases(getConString(drConfig));
            string tempConString = ""; string tempDBString = ""; //string tempSchemaString = "";
            string keyString = drConfig["db_key_string"].ToString();
            int schemaCount = -1;
            if (tblDatabases != null)
            {
                //foreach(DataRow dr in tblDatabases.
                for (int i = 1; i <= allowedDBCount; i++)
                {
                    tempDBString = keyString + "_" + i.ToString("00");
                    DataRow[] dr_dbs = tblDatabases.Select("database_name='" + tempDBString + "'");
                    if (dr_dbs.Length > 0)//db_exists, so check for max schema count
                    {
                        schemaCount = getSchemaCount(getConString(drConfig, tempDBString));
                        if (schemaCount < allowedSchemaCount)
                        {
                            found = true;
                            break;
                        }
                    }
                    else//db doesn't exists, so take this DB
                    {
                        found = true;
                        break;
                    }
                }
            }
            if (found)
                DB_Name = tempDBString;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in checkServerAvailability function. " + e.Message);
        }
        return found;
    }
    private DataTable getDatabases(string conString)
    {
        try
        {
            SqlConnection sqlConn = new SqlConnection(conString);
            sqlConn.Open();
            DataTable tblDatabases = sqlConn.GetSchema("Databases");
            sqlConn.Close();
            return tblDatabases;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in getDatabases function. " + e.Message);
            return null;
        }
    }
    private int getSchemaCount(string conString)
    {
        int ret = -1;
        try
        {
            DataSet ds = getSchemaNames(conString);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    ret = ds.Tables[0].Rows.Count;
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in getSchemaCount function. " + e.Message);
        }
        return ret;
    }
    private DataSet getSchemaNames(string conString)
    {
        //"select distinct schema_name(schema_id) as schemas from sys.tables"
        DataSet ds = new DataSet();
        try
        {
            HDDatabase hdd = new HDDatabase(conString);
            ds = hdd.GetDataset("select distinct schema_name(schema_id) as schemas from sys.tables");
            hdd = null;
            return ds;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in getSchemaNames function. " + e.Message);
            return ds;
        }
    }
    private int getDBCount(string conString)
    {
        try
        {
            DataTable tblDatabases = getDatabases(conString);
            //foreach (DataRow row in tblDatabases.Rows)
            //    MessageBox.Show(row["database_name"].ToString());                         
            return tblDatabases.Rows.Count;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in getDBCount function. " + e.Message);
            return -1;
        }
    }
    private string getConString(DataRow drConfig)
    {
        return "Data Source=" + drConfig["server"].ToString() + ";User Id=" + drConfig["db_username"].ToString() + ";Password=" + drConfig["db_password"].ToString();
    }
    private string getConString(DataRow drConfig, string db_name)
    {
        return "Data Source=" + drConfig["server"].ToString() + ";Initial Catalog=" + db_name + ";User Id=" + drConfig["db_username"].ToString() + ";Password=" + drConfig["db_password"].ToString();
    }
    #endregion
}